﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Matrix.Common.DBUtility;
using Matrix.MaKaYunShang.Model;
using Matrix.MaKaYunShang.Model.Dto;
using Matrix.MaKaYunShang.Model.Enum;

namespace Matrix.MaKaYunShang.DAL
{
    public partial class UserCardDAL : BaseDAL<UserCardDAL>
    {
        #region 默认方法
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(UserCard model)
        {
            var strSql = new StringBuilder();
            strSql.Append("insert into UserCard(");
            strSql.Append("CloudCardId,ReceiveUserId,CardCode,ShopId,ReceiveSource,ReceiveIp,Longitude,Latitude,CardStatus,DonationUserId,BeforeDonationUserCardId,VerifyTime,VerifyShopId,VerifyMerchantUserId,VerifyIp,UserAddressId,SendType,LogisticsNum,LogisticsCompany,RedSendSerialNumber,SellingPrice,BuyUserId,AfterSellingUserCardId,SellingDealTime,SellingListedTime,UpdateTime,UpdateOperat,SendCardShopId,AddTime,IsDelete");
            strSql.Append(") values (");
            strSql.Append("@CloudCardId,@ReceiveUserId,@CardCode,@ShopId,@ReceiveSource,@ReceiveIp,@Longitude,@Latitude,@CardStatus,@DonationUserId,@BeforeDonationUserCardId,@VerifyTime,@VerifyShopId,@VerifyMerchantUserId,@VerifyIp,@UserAddressId,@SendType,@LogisticsNum,@LogisticsCompany,@RedSendSerialNumber,@SellingPrice,@BuyUserId,@AfterSellingUserCardId,@SellingDealTime,@SellingListedTime,@UpdateTime,@UpdateOperat,@SendCardShopId,@AddTime,@IsDelete");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
			            new SqlParameter("@CloudCardId", SqlDbType.Int,4) ,            
                        new SqlParameter("@ReceiveUserId", SqlDbType.Int,4) ,            
                        new SqlParameter("@CardCode", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@ShopId", SqlDbType.Int,4) ,            
                        new SqlParameter("@ReceiveSource", SqlDbType.Int,4) ,            
                        new SqlParameter("@ReceiveIp", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@Longitude", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@Latitude", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@CardStatus", SqlDbType.Int,4) ,            
                        new SqlParameter("@DonationUserId", SqlDbType.Int,4) ,            
                        new SqlParameter("@BeforeDonationUserCardId", SqlDbType.Int,4) ,            
                        new SqlParameter("@VerifyTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@VerifyShopId", SqlDbType.Int,4) ,            
                        new SqlParameter("@VerifyMerchantUserId", SqlDbType.Int,4) ,            
                        new SqlParameter("@VerifyIp", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@UserAddressId", SqlDbType.Int,4) ,            
                        new SqlParameter("@SendType", SqlDbType.Int,4) ,            
                        new SqlParameter("@LogisticsNum", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@LogisticsCompany", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@RedSendSerialNumber", SqlDbType.NVarChar,64) ,            
                        new SqlParameter("@SellingPrice", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@BuyUserId", SqlDbType.Int,4) ,            
                        new SqlParameter("@AfterSellingUserCardId", SqlDbType.Int,4) ,            
                        new SqlParameter("@SellingDealTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@SellingListedTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@UpdateTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@UpdateOperat", SqlDbType.Int,4) ,            
                        new SqlParameter("@SendCardShopId", SqlDbType.Int,4) ,            
                        new SqlParameter("@AddTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.CloudCardId;
            parameters[1].Value = model.ReceiveUserId;
            parameters[2].Value = model.CardCode;
            parameters[3].Value = model.ShopId;
            parameters[4].Value = model.ReceiveSource;
            parameters[5].Value = model.ReceiveIp;
            parameters[6].Value = model.Longitude;
            parameters[7].Value = model.Latitude;
            parameters[8].Value = model.CardStatus;
            parameters[9].Value = model.DonationUserId;
            parameters[10].Value = model.BeforeDonationUserCardId;
            parameters[11].Value = model.VerifyTime;
            parameters[12].Value = model.VerifyShopId;
            parameters[13].Value = model.VerifyMerchantUserId;
            parameters[14].Value = model.VerifyIp;
            parameters[15].Value = model.UserAddressId;
            parameters[16].Value = model.SendType;
            parameters[17].Value = model.LogisticsNum;
            parameters[18].Value = model.LogisticsCompany;
            parameters[19].Value = model.RedSendSerialNumber;
            parameters[20].Value = model.SellingPrice;
            parameters[21].Value = model.BuyUserId;
            parameters[22].Value = model.AfterSellingUserCardId;
            parameters[23].Value = model.SellingDealTime;
            parameters[24].Value = model.SellingListedTime;
            parameters[25].Value = model.UpdateTime;
            parameters[26].Value = model.UpdateOperat;
            parameters[27].Value = model.SendCardShopId;
            parameters[28].Value = model.AddTime;
            parameters[29].Value = model.IsDelete; var obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(UserCard model)
        {
            var strSql = new StringBuilder();
            strSql.Append("update UserCard set ");

            strSql.Append(" CloudCardId = @CloudCardId , ");
            strSql.Append(" ReceiveUserId = @ReceiveUserId , ");
            strSql.Append(" CardCode = @CardCode , ");
            strSql.Append(" ShopId = @ShopId , ");
            strSql.Append(" ReceiveSource = @ReceiveSource , ");
            strSql.Append(" ReceiveIp = @ReceiveIp , ");
            strSql.Append(" Longitude = @Longitude , ");
            strSql.Append(" Latitude = @Latitude , ");
            strSql.Append(" CardStatus = @CardStatus , ");
            strSql.Append(" DonationUserId = @DonationUserId , ");
            strSql.Append(" BeforeDonationUserCardId = @BeforeDonationUserCardId , ");
            strSql.Append(" VerifyTime = @VerifyTime , ");
            strSql.Append(" VerifyShopId = @VerifyShopId , ");
            strSql.Append(" VerifyMerchantUserId = @VerifyMerchantUserId , ");
            strSql.Append(" VerifyIp = @VerifyIp , ");
            strSql.Append(" UserAddressId = @UserAddressId , ");
            strSql.Append(" SendType = @SendType , ");
            strSql.Append(" LogisticsNum = @LogisticsNum , ");
            strSql.Append(" LogisticsCompany = @LogisticsCompany , ");
            strSql.Append(" RedSendSerialNumber = @RedSendSerialNumber , ");
            strSql.Append(" SellingPrice = @SellingPrice , ");
            strSql.Append(" BuyUserId = @BuyUserId , ");
            strSql.Append(" AfterSellingUserCardId = @AfterSellingUserCardId , ");
            strSql.Append(" SellingDealTime = @SellingDealTime , ");
            strSql.Append(" SellingListedTime = @SellingListedTime , ");
            strSql.Append(" UpdateTime = @UpdateTime , ");
            strSql.Append(" UpdateOperat = @UpdateOperat , ");
            strSql.Append(" SendCardShopId = @SendCardShopId , ");
            strSql.Append(" AddTime = @AddTime , ");
            strSql.Append(" IsDelete = @IsDelete  ");
            strSql.Append(" where Id=@Id ");

            SqlParameter[] parameters = {
			            new SqlParameter("@Id", SqlDbType.Int,4) ,            
                        new SqlParameter("@CloudCardId", SqlDbType.Int,4) ,            
                        new SqlParameter("@ReceiveUserId", SqlDbType.Int,4) ,            
                        new SqlParameter("@CardCode", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@ShopId", SqlDbType.Int,4) ,            
                        new SqlParameter("@ReceiveSource", SqlDbType.Int,4) ,            
                        new SqlParameter("@ReceiveIp", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@Longitude", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@Latitude", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@CardStatus", SqlDbType.Int,4) ,            
                        new SqlParameter("@DonationUserId", SqlDbType.Int,4) ,            
                        new SqlParameter("@BeforeDonationUserCardId", SqlDbType.Int,4) ,            
                        new SqlParameter("@VerifyTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@VerifyShopId", SqlDbType.Int,4) ,            
                        new SqlParameter("@VerifyMerchantUserId", SqlDbType.Int,4) ,            
                        new SqlParameter("@VerifyIp", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@UserAddressId", SqlDbType.Int,4) ,            
                        new SqlParameter("@SendType", SqlDbType.Int,4) ,            
                        new SqlParameter("@LogisticsNum", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@LogisticsCompany", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@RedSendSerialNumber", SqlDbType.NVarChar,64) ,            
                        new SqlParameter("@SellingPrice", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@BuyUserId", SqlDbType.Int,4) ,            
                        new SqlParameter("@AfterSellingUserCardId", SqlDbType.Int,4) ,            
                        new SqlParameter("@SellingDealTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@SellingListedTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@UpdateTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@UpdateOperat", SqlDbType.Int,4) ,            
                        new SqlParameter("@SendCardShopId", SqlDbType.Int,4) ,            
                        new SqlParameter("@AddTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.Id;
            parameters[1].Value = model.CloudCardId;
            parameters[2].Value = model.ReceiveUserId;
            parameters[3].Value = model.CardCode;
            parameters[4].Value = model.ShopId;
            parameters[5].Value = model.ReceiveSource;
            parameters[6].Value = model.ReceiveIp;
            parameters[7].Value = model.Longitude;
            parameters[8].Value = model.Latitude;
            parameters[9].Value = model.CardStatus;
            parameters[10].Value = model.DonationUserId;
            parameters[11].Value = model.BeforeDonationUserCardId;
            parameters[12].Value = model.VerifyTime;
            parameters[13].Value = model.VerifyShopId;
            parameters[14].Value = model.VerifyMerchantUserId;
            parameters[15].Value = model.VerifyIp;
            parameters[16].Value = model.UserAddressId;
            parameters[17].Value = model.SendType;
            parameters[18].Value = model.LogisticsNum;
            parameters[19].Value = model.LogisticsCompany;
            parameters[20].Value = model.RedSendSerialNumber;
            parameters[21].Value = model.SellingPrice;
            parameters[22].Value = model.BuyUserId;
            parameters[23].Value = model.AfterSellingUserCardId;
            parameters[24].Value = model.SellingDealTime;
            parameters[25].Value = model.SellingListedTime;
            parameters[26].Value = model.UpdateTime;
            parameters[27].Value = model.UpdateOperat;
            parameters[28].Value = model.SendCardShopId;
            parameters[29].Value = model.AddTime;
            parameters[30].Value = model.IsDelete; return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("update UserCard set IsDelete=1 ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 批量删除一批数据
        /// </summary>
        public bool DeleteList(string Idlist)
        {
            var strSql = new StringBuilder();
            strSql.Append("update UserCard set IsDelete=1 ");
            strSql.Append(" where Id in (" + Idlist + ")  ");
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataSet GetModel(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("select Id, CloudCardId, ReceiveUserId, CardCode, ShopId, ReceiveSource, ReceiveIp, Longitude, Latitude, CardStatus, DonationUserId, BeforeDonationUserCardId, VerifyTime, VerifyShopId, VerifyMerchantUserId, VerifyIp, UserAddressId, SendType, LogisticsNum, LogisticsCompany, RedSendSerialNumber, SellingPrice, BuyUserId, AfterSellingUserCardId, SellingDealTime, SellingListedTime, UpdateTime, UpdateOperat, SendCardShopId, AddTime, IsDelete  ");
            strSql.Append("  from UserCard ");
            strSql.Append(" where Id=@Id  and IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            var ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            return ds.Tables[0].Rows.Count > 0 ? ds : null;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM UserCard where IsDelete=0 ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" and " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion

        #region 扩展方法

        /// <summary>
        /// 用户可用卡卷数量
        /// </summary>
        public int GetUsableQrCodeCount(int receiveUserId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(
                "select count(1) from UserCard  left join CloudCards cc on cc.Id=UserCard.CloudCardId where receiveUserId=@receiveUserId  and CardStatus=@CardStatus and  cc.EndTime>getDate()  ");
            SqlParameter[] parameters =
            {
                new SqlParameter("@receiveUserId", SqlDbType.Int),
                new SqlParameter("@CardStatus", SqlDbType.Int)
            };
            parameters[0].Value = receiveUserId;
            parameters[1].Value = (int)CloudCardEnum.UserCardStatus.未使用;
            var num = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            return num != null ? int.Parse(num.ToString()) : 0;
        }

        /// <summary>
        /// 用户卡卷列表
        /// </summary>
        /// <param name="userId">领用人用户id</param>
        /// <param name="cardStatus">卡卷状态</param>
        /// <remarks>create 2016-06-12 createPerson: lk </remarks>
        public List<UserCard> GetUserCardList(int pageIndex, int pageSize, int userId, int cardStatus,
            ref int totalCount)
        {
            string strWhere = " u.IsDelete=0 ";
            string table =
                " UserCard u left join CloudCards cc on cc.Id=u.CloudCardId    left join shops s on s.id=cc.shopId";

            if (cardStatus == (int)CloudCardEnum.UserCardStatus.已过期)
            {
                strWhere += " and CC.EndTime<getDate() and   u.CardStatus=" + (int)CloudCardEnum.UserCardStatus.未使用;
                table = " UserCard u left join CloudCards cc on cc.Id=u.CloudCardId  left join shops s on s.id=cc.shopId";
            }
            if (cardStatus == (int)CloudCardEnum.UserCardStatus.已转赠)
            {
                strWhere += " and u.CardStatus=" + (int)CloudCardEnum.UserCardStatus.已转赠;

            }
            if (cardStatus == (int)CloudCardEnum.UserCardStatus.未使用)
            {
                strWhere += "and CC.EndTime>getDate()  and CardStatus=" + cardStatus;

            }
            if (cardStatus == (int)CloudCardEnum.UserCardStatus.已核销)
            {
                strWhere += " and CardStatus=" + cardStatus;

            }
            strWhere += " and ReceiveUserId=" + userId;
            const string selectColumus =
                "CategoryCode,CardValue ,DiscountValue,ShopDescription,Instruction,temp.ShopName,temp.CardValue,Id,[CloudCardId] ,[ReceiveUserId],[CardCode] ,[ShopId],[ReceiveSource],[ReceiveIp] ,[Longitude],[Latitude]   ,[CardStatus] ,[DonationUserId] ," +
                "[BeforeDonationUserCardId]  ,[VerifyTime] , [VerifyShopId],[VerifyMerchantUserId] ,[VerifyIp]  ,[UserAddressId]  ,[SendType] ,[LogisticsNum] ,[LogisticsCompany] ," +
                "[RedSendSerialNumber],[SellingPrice],[BuyUserId] ,[AfterSellingUserCardId] ,[SellingDealTime]  ,[SellingListedTime]  ,[UpdateTime] ,[UpdateOperat],[SendCardShopId] ,[AddTime] ,[IsDelete] ,LoGo,Title,StartTime,EndTime,CardName";
            const string selectColumus2 =
                " cc.CategoryCode,cc.DiscountValue,cc.Instruction,s.Description as ShopDescription,s.Address AS ShopAddress,s.Tel as ShopTel,s.Name as ShopName,cc.CardValue,u.Id,[CloudCardId] ,[ReceiveUserId],[CardCode] ,u.[ShopId],[ReceiveSource],[ReceiveIp] ,u.[Longitude],u.[Latitude]  ,[CardStatus] ,[DonationUserId] ,[BeforeDonationUserCardId]  ,[VerifyTime], [VerifyShopId],[VerifyMerchantUserId] ,[VerifyIp]  ,[UserAddressId]  ,[SendType] ,[LogisticsNum] ,[LogisticsCompany] ,[RedSendSerialNumber],[SellingPrice],[BuyUserId]   ,[AfterSellingUserCardId] ,[SellingDealTime]  ,[SellingListedTime]  ,[UpdateTime] ,[UpdateOperat],[SendCardShopId] ,u.[AddTime] ,u.[IsDelete]  ,cc.LoGo,Title,StartTime,EndTime,CardName ";
            return GetSimpleListPage<UserCard>(pageIndex, pageSize, table, selectColumus, selectColumus2,
                "u.AddTime desc", strWhere, ref totalCount);
        }

        /// <summary>
        /// 根据类别查询用户 卡卷记录
        /// </summary>
        /// <returns></returns>
        /// <remarks>create lk 2016-06-12</remarks>
        public List<UserCard> GetUserCardByCategoryCodeList(int pageIndex, int pageSize, int userId, ref int totalCount,
            string categoryCodeStr, string keyWord, int cardStatus)
        {
            var strWhere =
                " u.IsDelete=0and (u.CardCode like @keyWord or u.CardStatus like @keyWord or u.LogisticsNum like @keyWord or u.LogisticsCompany like @keyWord or cc.CardName like @keyWord)";
            string table = " UserCard u ";
            if (!string.IsNullOrEmpty(categoryCodeStr))
            {
                string[] categoryCode = categoryCodeStr.Split(',');
                int i = 0;
                strWhere += " and (";
                foreach (var item in categoryCode)
                {
                    i++;
                    if (i >= 2)
                    {
                        strWhere += " or ccay.categoryCode=" + item;
                    }
                    else
                    {
                        strWhere += "  ccay.categoryCode=" + item;
                    }
                }
                strWhere += ")";
                strWhere += "  and u.ReceiveUserId=@userId";
                if (cardStatus >= 0)
                {
                    strWhere += "  and u.CardStatus=" + cardStatus;
                }
                table = " UserCard u  left join CloudCards cc on cc.Id=u.CloudCardId   left join CardCategory ccay on ccay.Id=cc.CardCategoryId left join ConsumerAddress ua on ua.Id=u.UserAddressId left join Shops m on m.Id=u.Id ";
            }
            SqlParameter[] parameters =
            {
                new SqlParameter("@userId", SqlDbType.Int, 4),
                new SqlParameter("@keyWord", SqlDbType.NVarChar)
            };
            parameters[0].Value = userId;
            parameters[1].Value = "%" + keyWord + "%";
            const string selectColumus = "*    ";
            const string selectColumus2 =
                " u.*  ,cc.LoGo,cc.Title,cc.CategoryCode,cc.CardName,ua.LinkMan,ua.LinkMobile,ua.Address,m.Name ";
            return GetSimpleListPage<UserCard>(pageIndex, pageSize, table, selectColumus, selectColumus2,
                "u.AddTime desc", strWhere, ref totalCount, parameters);
        }

        /// <summary>
        /// 查询卡劵详细信息
        /// </summary>
        /// <remarks>create lk 2016-06-12</remarks>
        public DataSet GetModelByUserCardId(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT s.Description as ShopDescription,s.Address AS ShopAddress,  s.Tel as ShopTel,s.Name as ShopName,  ");
            strSql.Append("s.Logo as ShopLoGo,cc.CardValue, uc.Id,  uc.CloudCardId,  uc.ReceiveUserId,  uc.CardCode,  uc.ShopId,   ");
            strSql.Append("uc.ReceiveSource,  uc.ReceiveIp,  uc.Longitude,  uc.Latitude,  uc.CardStatus,uc.DonationUserId,   ");
            strSql.Append("uc.BeforeDonationUserCardId,  uc.VerifyTime,  uc.VerifyShopId,  uc.VerifyMerchantUserId,uc.VerifyIp,   ");
            strSql.Append(" uc.UserAddressId,  uc.SendType,  uc.LogisticsNum,  uc.LogisticsCompany,uc.RedSendSerialNumber,  uc.SellingPrice,  ");
            strSql.Append("uc.BuyUserId,  uc.AfterSellingUserCardId, uc.SellingDealTime,  uc.SellingListedTime,  uc.UpdateTime, uc.UpdateOperat, ");
            strSql.Append("uc.SendCardShopId, uc.AddTime,  uc.IsDelete,  cc.CardName,  cc.CardCategoryId,  cc.CardKey,cc.LoGo,  cc.Title,  ");
            strSql.Append("cc.StartTime,  cc.EndTime,  cc.PassStatus,Instruction,Disclaimer,cc.Description,cc.CategoryCode,cc.CanShare ");
            strSql.Append("FROM  CloudCards cc INNER JOIN  UserCard uc ON  cc.Id =  uc.CloudCardId left join Shops s on s.id=cc.shopId  ");
            strSql.Append("where  uc.Id=@Id  and  uc.IsDelete=0  ");
            SqlParameter[] parameters =
            {
                new SqlParameter("@Id", SqlDbType.Int, 4)
            };
            parameters[0].Value = id;

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 查询卡劵详细信息
        /// </summary>
        /// <remarks>create lk 2016-06-12</remarks>
        public DataSet GetModelByCardCode(string cardCode)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT    ");
            strSql.Append("cc.UsableShopIds,uc.Id,uc.CloudCardId,uc.ReceiveUserId,  uc.CardCode,uc.ShopId, uc.ReceiveSource,uc.ReceiveIp,uc.Longitude, ");
            strSql.Append("uc.Latitude,  uc.CardStatus,uc.DonationUserId,uc.BeforeDonationUserCardId,uc.VerifyTime, uc.VerifyShopId,uc.VerifyMerchantUserId,uc.VerifyIp, ");
            strSql.Append("uc.UserAddressId,  uc.SendType,  uc.LogisticsNum,  uc.LogisticsCompany, uc.RedSendSerialNumber,  uc.SellingPrice, uc.BuyUserId,   ");
            strSql.Append("uc.AfterSellingUserCardId,  uc.SellingDealTime,  uc.SellingListedTime,  uc.UpdateTime,  uc.UpdateOperat,  uc.SendCardShopId,  uc.AddTime, ");
            strSql.Append("uc.IsDelete,  cc.CardName,  cc.CardCategoryId,  cc.CardKey,  cc.CardValue, cc.LoGo,  cc.Title,  cc.StartTime,   ");
            strSql.Append("uc.SellingDealTime, uc.SellingListedTime, uc.UpdateTime,uc.UpdateOperat, uc.SendCardShopId, ");
            strSql.Append("cc.EndTime,  cc.PassStatus,Instruction,Disclaimer, cc.Description FROM   ");
            strSql.Append("CloudCards  cc INNER JOIN   UserCard uc ON  cc.Id =  uc.CloudCardId  ");
            strSql.Append("where  uc.CardCode=@cardCode  and uc.IsDelete=0 ");
            SqlParameter[] parameters =
            {
                new SqlParameter("@cardCode", SqlDbType.NVarChar)
            };
            parameters[0].Value = cardCode;
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 查询卡劵id每天的扫码次数
        /// </summary>
        /// <remarks>create lk 2016-06-13</remarks>
        public int GetModelByCloudCardIdCount(int cloudCardId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(
                " SELECT  COUNT(1)  FROM [UserCard] WHERE  convert(char(8),AddTime,112) = convert(char(8),getdate(),112)  ");
            strSql.Append(" and CloudCardId=@CloudCardId and IsDelete=0 ");
            SqlParameter[] parameters =
            {
                new SqlParameter("@CloudCardId", SqlDbType.Int, 4)
            };
            parameters[0].Value = cloudCardId;
            object num = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (num != null)
            {
                return int.Parse(num.ToString());
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// 查询核销记录日期列表
        /// </summary>
        /// <remarks>create lk 2016-06-21</remarks>
        public DataSet GetVerifyDataList(int verifyMerchantUserId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT   Year([AddTime]) as VerifyYear,Month([AddTime]) as VerifyMonth ");
            strSql.Append(
                " FROM [UserCard] where VerifyMerchantUserId=@verifyMerchantUserId and  CardStatus=1 and IsDelete=0 group by Month([AddTime]),Year([AddTime]) order by Year([AddTime]),Month([AddTime]) desc ");
            SqlParameter[] parameters =
            {
                new SqlParameter("@verifyMerchantUserId", SqlDbType.Int, 4)
            };
            parameters[0].Value = verifyMerchantUserId;

            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }

        /// <remarks>create lk 2016-06-21</remarks>
        public DataSet GetModelByStrWhere(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT UC.*, CC.CardName, CC.CardCategoryId, CC.CardKey, ");
            strSql.Append("CC.LoGo, CC.Title, CC.StartTime, CC.EndTime,  ");
            strSql.Append("CC.PassStatus,Instruction,Disclaimer FROM CloudCards CC INNER JOIN   ");
            strSql.Append("UserCard UC ON CC.Id = UC.CloudCardId   ");
            strSql.Append("where UC.IsDelete=0  ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" and " + strWhere);
            }
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 查询可以发送红包的卡劵
        /// </summary>
        /// <remarks>create lk 2016-06-26</remarks>
        public DataSet GetMdelByCategoryCode(string categoryCode, int cardStatus)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT uc.*,cc.CardValue ");
            strSql.Append(" FROM UserCard uc left join CloudCards cc on uc.CloudCardId=cc.Id left join ");
            strSql.Append(" CardCategory cct on cct.Id=cc.CardCategoryId where  ");
            strSql.Append(" cct.CategoryCode=@CategoryCode and  uc.CardStatus=@CardStatus  and (uc.RedSendSerialNumber is  null or uc.RedSendSerialNumber='')");
            SqlParameter[] parameters =
            {
                new SqlParameter("@CategoryCode", SqlDbType.NVarChar),
                new SqlParameter("@CardStatus", SqlDbType.Int, 4)
            };
            parameters[0].Value = categoryCode;
            parameters[1].Value = cardStatus;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <remarks>2016-6-29 万乐园 创建</remarks>
        public List<UserCard> GetList(int pageIndex, int pageSize, string keyWord, int cloudCardId, int shopId,
            int cardStatus, ref int totalCount)
        {
            var strWhere = " IsDelete=0 and [CardCode] like @keyWord";
            if (cloudCardId > 0)
            {
                strWhere += " and CloudCardId=" + cloudCardId;
            }
            if (shopId >= 0)
            {
                strWhere += " and ShopId=" + shopId;
            }
            if (cardStatus >= 0)
            {
                strWhere += " and CardStatus=" + cardStatus;
            }
            var parameters = new[] { new SqlParameter { ParameterName = "@keyWord", Value = "%" + keyWord + "%" } };
            return GetSimpleListPage<UserCard>(pageIndex, pageSize, "UserCard", null, null, "Id desc", strWhere,
                ref totalCount, parameters);
        }

        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <returns></returns>
        public List<ViewUserCardDto> GetPagingList(int pageIndex, int pageSize, string searchKeyWord, string colOrder, int cardStatus, string shopIds, bool isOnlySearchVerifyShop, string startTime, string endTime, int cateId, ref int totalCount)
        {
            var strWhere = new StringBuilder(" IsDelete=0 ");
            var parameters = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(searchKeyWord))
            {
                strWhere.Append(
                    " and ( CardCode like @keyWord or CloudCardId in (select Id from CloudCards where CardName like @keyWord and IsDelete=0))");
                parameters.Add(new SqlParameter { ParameterName = "@keyWord", Value = "%" + searchKeyWord + "%" });
            }
            if (cateId > 0)
            {
                strWhere.Append(" and CloudCardId in (select Id from CloudCards where IsDelete=0 and CardCategoryId=" + cateId + ")");
            }
            if (cardStatus >= 0)
            {
                strWhere.Append(" and CardStatus=" + cardStatus);
            }
            if (!string.IsNullOrEmpty(shopIds))
            {
                if (isOnlySearchVerifyShop)
                {
                    strWhere.Append(" and VerifyShopId in (" + shopIds.Trim(',') + ")");
                }
                else
                {
                    strWhere.Append(" and (ShopId in (" + shopIds.Trim(',') + ") or VerifyShopId in (" + shopIds.Trim(',') + "))");
                }
            }
            if (!string.IsNullOrEmpty(startTime))
            {
                strWhere.Append(" and AddTime>=@StartTime");
                parameters.Add(new SqlParameter { ParameterName = "@StartTime", Value = startTime });
            }
            if (!string.IsNullOrEmpty(endTime))
            {
                strWhere.Append(" and AddTime<=@EndTime");
                endTime = DateTime.Parse(endTime).AddDays(1).ToString();
                parameters.Add(new SqlParameter { ParameterName = "@EndTime", Value = endTime });
            }
            return GetSimpleListPage<ViewUserCardDto>(pageIndex, pageSize, "UserCard t1", null,
                "Id, (select CardName from CloudCards t2 where t2.Id=t1.CloudCardId) as CardName, CardCode, CardStatus, AddTime, VerifyTime, (select Name from Shops t3 where t3.Id=t1.ShopId) as ShopName, (select NickName from Consumer t4 where t4.Id=t1.ReceiveUserId) as ConsumerNickName, (select UserName from McAccounts t5 where t5.Id=t1.VerifyMerchantUserId) as VerifyUserName, (select CategoryName from CardCategory t6 where t6.Id=(select CardCategoryId from CloudCards t7 where t7.Id=t1.CloudCardId)) as CardCategoryName, (select Name from Shops t8 where t8.Id=t1.VerifyShopId) as VerifyShopName",
                colOrder, strWhere.ToString(), ref totalCount, parameters.ToArray());
        }
        public List<ViewUserCardDto> GetPagingList(int pageIndex, int pageSize, string searchKeyWord, string colOrder,
            string categoryCodeStr, string shopIds, int cardStatus, ref int totalCount)
        {
            var strWhere = " t1.IsDelete=0 ";
            var parameters = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(searchKeyWord))
            {
                strWhere +=
                    " and (VerifyTime like @keyWord or LogisticsCompany like @keyWord or LogisticsNum like @keyWord or CardCode like @keyWord or CloudCardId in (select Id from CloudCards where CardName like @keyWord and IsDelete=0))";
                parameters.Add(new SqlParameter { ParameterName = "@keyWord", Value = "%" + searchKeyWord + "%" });
            }
            if (!string.IsNullOrEmpty(categoryCodeStr))
            {
                string[] categoryCode = categoryCodeStr.Split(',');
                int i = 0;
                strWhere += " and (";
                foreach (var item in categoryCode)
                {
                    i++;
                    if (i >= 2)
                    {
                        strWhere += " or ccay.categoryCode=" + item;
                    }
                    else
                    {
                        strWhere += "  ccay.categoryCode=" + item;
                    }
                }
                strWhere += " )";
            }
            if (cardStatus >= 0)
            {
                strWhere += " and CardStatus=" + cardStatus;
            }
            if (!string.IsNullOrEmpty(shopIds))
            {
                strWhere += " and t1.ShopId in (" + shopIds.Trim(',') + ")";
            }
            return GetSimpleListPage<ViewUserCardDto>(pageIndex, pageSize, "UserCard t1 left join   CloudCards ccay on ccay.id=t1.CloudCardId left join ConsumerAddress ca on ca.id=t1.UserAddressId ", null,
                "('姓名:'+ca.LinkMan+'  电话:'+ca.LinkMobile+LinkMobile+'  地址:'+AddressDetail) as ConsumerAddress,LogisticsNum,LogisticsCompany, t1.Id, t1.ShopId,(select CardName from CloudCards t2 where t2.Id=t1.CloudCardId) as CardName, CardCode, CardStatus, t1.AddTime, VerifyTime, (select Name from Shops t3 where t3.Id=t1.ShopId) as ShopName ",
                colOrder, strWhere.ToString(), ref totalCount, parameters.ToArray());
        }

        #endregion
    }
}



